package com.nbsaas.boot.order.data.mapper;

import com.nbsaas.boot.order.data.entity.Order;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

/**
 * 订单查询Mapper接口
 * 使用Freemarker动态生成
 */
@Mapper
public interface OrderQueryMapper {

    /**
     * 基础订单查询
     * @param shopId 店铺ID
     * @param orderState 订单状态
     * @return 订单列表
     */
    @Select("""
        SELECT 
            model.id,
            model.bill_no,
            model.add_date,
            model.customer_id,
            model.total_amount,
            model.amount,
            model.pay_amount,
            model.pay_state,
            model.order_state,
            model.shop_id,
            model.name
        FROM bs_order model
        WHERE 1=1
            AND model.shop_id = #{shopId}
            AND model.order_state = #{orderState}
        ORDER BY model.add_date DESC
    """)
    List<Order> findOrdersByShopAndState(@Param("shopId") Long shopId, @Param("orderState") Integer orderState);

    /**
     * 订单统计查询
     * @param shopId 店铺ID
     * @param startDate 开始日期
     * @param endDate 结束日期
     * @return 统计结果
     */
    @Select("""
        SELECT 
            COUNT(model.id) as total_count,
            SUM(model.total_amount) as total_amount,
            SUM(model.pay_amount) as total_pay_amount,
            AVG(model.total_amount) as avg_amount
        FROM bs_order model
        WHERE 1=1
            AND model.shop_id = #{shopId}
            AND model.add_date >= #{startDate}
            AND model.add_date <= #{endDate}
            AND model.order_state IN (1, 2, 3)
    """)
    Map<String, Object> getOrderStatistics(@Param("shopId") Long shopId, 
                                          @Param("startDate") LocalDateTime startDate, 
                                          @Param("endDate") LocalDateTime endDate);

    /**
     * 客户订单查询
     * @param customerId 客户ID
     * @param shopId 店铺ID
     * @return 客户订单列表
     */
    @Select("""
        SELECT 
            model.id,
            model.bill_no,
            model.add_date,
            model.total_amount,
            model.pay_amount,
            model.order_state,
            model.pay_state,
            model.finish_time
        FROM bs_order model
        WHERE 1=1
            AND model.customer_id = #{customerId}
            AND model.shop_id = #{shopId}
        ORDER BY model.add_date DESC
    """)
    List<Order> findOrdersByCustomer(@Param("customerId") Long customerId, @Param("shopId") Long shopId);

    /**
     * 支付状态查询
     * @param payState 支付状态
     * @param shopId 店铺ID
     * @param startDate 开始日期
     * @return 支付订单列表
     */
    @Select("""
        SELECT 
            model.id,
            model.bill_no,
            model.pay_amount,
            model.pay_state,
            model.pay_method,
            model.pay_no,
            model.online_pay,
            model.balance,
            model.travel_balance
        FROM bs_order model
        WHERE 1=1
            AND model.pay_state = #{payState}
            AND model.shop_id = #{shopId}
            AND model.add_date >= #{startDate}
        ORDER BY model.add_date DESC
    """)
    List<Map<String, Object>> findOrdersByPayState(@Param("payState") Integer payState, 
                                                   @Param("shopId") Long shopId, 
                                                   @Param("startDate") LocalDateTime startDate);

    /**
     * 订单金额统计（按日期分组）
     * @param shopId 店铺ID
     * @param startDate 开始日期
     * @param endDate 结束日期
     * @return 按日期分组的统计结果
     */
    @Select("""
        SELECT 
            DATE(model.add_date) as order_date,
            COUNT(model.id) as order_count,
            SUM(model.total_amount) as total_amount,
            SUM(model.pay_amount) as pay_amount,
            SUM(model.discount) as total_discount
        FROM bs_order model
        WHERE 1=1
            AND model.shop_id = #{shopId}
            AND model.add_date >= #{startDate}
            AND model.add_date <= #{endDate}
        GROUP BY DATE(model.add_date)
        ORDER BY order_date DESC
    """)
    List<Map<String, Object>> getOrderStatisticsByDate(@Param("shopId") Long shopId, 
                                                       @Param("startDate") LocalDateTime startDate, 
                                                       @Param("endDate") LocalDateTime endDate);

    /**
     * 优惠券使用统计
     * @param shopId 店铺ID
     * @param startDate 开始日期
     * @return 优惠券使用订单列表
     */
    @Select("""
        SELECT 
            model.id,
            model.bill_no,
            model.coupon_money,
            model.shop_coupon_id,
            model.total_amount,
            model.pay_amount,
            model.add_date
        FROM bs_order model
        WHERE 1=1
            AND model.shop_coupon_id IS NOT NULL
            AND model.shop_id = #{shopId}
            AND model.add_date >= #{startDate}
        ORDER BY model.add_date DESC
    """)
    List<Map<String, Object>> findOrdersWithCoupon(@Param("shopId") Long shopId, 
                                                   @Param("startDate") LocalDateTime startDate);

    /**
     * 会员卡使用查询
     * @param shopId 店铺ID
     * @return 会员卡使用订单列表
     */
    @Select("""
        SELECT 
            model.id,
            model.bill_no,
            model.vip_card_id,
            model.vip_pay,
            model.total_amount,
            model.pay_amount,
            model.balance,
            model.add_date
        FROM bs_order model
        WHERE 1=1
            AND model.vip_card_id IS NOT NULL
            AND model.shop_id = #{shopId}
        ORDER BY model.add_date DESC
    """)
    List<Map<String, Object>> findOrdersWithVipCard(@Param("shopId") Long shopId);

    /**
     * 订单来源统计
     * @param shopId 店铺ID
     * @param startDate 开始日期
     * @param endDate 结束日期
     * @return 按来源分组的统计结果
     */
    @Select("""
        SELECT 
            model.order_source,
            COUNT(model.id) as order_count,
            SUM(model.total_amount) as total_amount,
            SUM(model.pay_amount) as pay_amount
        FROM bs_order model
        WHERE 1=1
            AND model.shop_id = #{shopId}
            AND model.add_date >= #{startDate}
            AND model.add_date <= #{endDate}
        GROUP BY model.order_source
    """)
    List<Map<String, Object>> getOrderStatisticsBySource(@Param("shopId") Long shopId, 
                                                         @Param("startDate") LocalDateTime startDate, 
                                                         @Param("endDate") LocalDateTime endDate);

    /**
     * 退款订单查询
     * @param shopId 店铺ID
     * @return 退款订单列表
     */
    @Select("""
        SELECT 
            model.id,
            model.bill_no,
            model.return_amount,
            model.return_count,
            model.total_amount,
            model.pay_amount,
            model.add_date,
            model.finish_time
        FROM bs_order model
        WHERE 1=1
            AND model.return_amount > 0
            AND model.shop_id = #{shopId}
        ORDER BY model.add_date DESC
    """)
    List<Map<String, Object>> findRefundOrders(@Param("shopId") Long shopId);

    /**
     * 积分使用统计
     * @param shopId 店铺ID
     * @param startDate 开始日期
     * @return 积分使用订单列表
     */
    @Select("""
        SELECT 
            model.id,
            model.bill_no,
            model.use_integral,
            model.send_integral,
            model.integral_discount,
            model.total_amount,
            model.pay_amount,
            model.add_date
        FROM bs_order model
        WHERE 1=1
            AND (model.use_integral > 0 OR model.send_integral > 0)
            AND model.shop_id = #{shopId}
            AND model.add_date >= #{startDate}
        ORDER BY model.add_date DESC
    """)
    List<Map<String, Object>> findOrdersWithIntegral(@Param("shopId") Long shopId, 
                                                     @Param("startDate") LocalDateTime startDate);

    /**
     * 红包使用查询
     * @param shopId 店铺ID
     * @return 红包使用订单列表
     */
    @Select("""
        SELECT 
            model.id,
            model.bill_no,
            model.red_packet_id,
            model.use_red_packet,
            model.red_packet_discount,
            model.total_amount,
            model.pay_amount,
            model.add_date
        FROM bs_order model
        WHERE 1=1
            AND model.red_packet_id IS NOT NULL
            AND model.shop_id = #{shopId}
        ORDER BY model.add_date DESC
    """)
    List<Map<String, Object>> findOrdersWithRedPacket(@Param("shopId") Long shopId);

    /**
     * 时间段订单统计
     * @param shopId 店铺ID
     * @param startDate 开始日期
     * @param endDate 结束日期
     * @return 按小时分组的统计结果
     */
    @Select("""
        SELECT 
            HOUR(model.add_date) as hour,
            COUNT(model.id) as order_count,
            SUM(model.total_amount) as total_amount,
            SUM(model.pay_amount) as pay_amount
        FROM bs_order model
        WHERE 1=1
            AND model.shop_id = #{shopId}
            AND model.add_date >= #{startDate}
            AND model.add_date <= #{endDate}
        GROUP BY HOUR(model.add_date)
        ORDER BY hour
    """)
    List<Map<String, Object>> getOrderStatisticsByHour(@Param("shopId") Long shopId, 
                                                       @Param("startDate") LocalDateTime startDate, 
                                                       @Param("endDate") LocalDateTime endDate);

    /**
     * 订单状态分布
     * @param shopId 店铺ID
     * @param startDate 开始日期
     * @param endDate 结束日期
     * @return 按状态分组的统计结果
     */
    @Select("""
        SELECT 
            model.order_state,
            COUNT(model.id) as order_count,
            SUM(model.total_amount) as total_amount,
            SUM(model.pay_amount) as pay_amount
        FROM bs_order model
        WHERE 1=1
            AND model.shop_id = #{shopId}
            AND model.add_date >= #{startDate}
            AND model.add_date <= #{endDate}
        GROUP BY model.order_state
    """)
    List<Map<String, Object>> getOrderStatisticsByState(@Param("shopId") Long shopId, 
                                                        @Param("startDate") LocalDateTime startDate, 
                                                        @Param("endDate") LocalDateTime endDate);

    /**
     * 支付方式统计
     * @param shopId 店铺ID
     * @param startDate 开始日期
     * @param endDate 结束日期
     * @return 按支付方式分组的统计结果
     */
    @Select("""
        SELECT 
            model.pay_method,
            COUNT(model.id) as order_count,
            SUM(model.pay_amount) as total_pay_amount,
            AVG(model.pay_amount) as avg_pay_amount
        FROM bs_order model
        WHERE 1=1
            AND model.shop_id = #{shopId}
            AND model.pay_state = 1
            AND model.add_date >= #{startDate}
            AND model.add_date <= #{endDate}
        GROUP BY model.pay_method
    """)
    List<Map<String, Object>> getOrderStatisticsByPayMethod(@Param("shopId") Long shopId, 
                                                            @Param("startDate") LocalDateTime startDate, 
                                                            @Param("endDate") LocalDateTime endDate);

    /**
     * 订单完成时间统计
     * @param shopId 店铺ID
     * @param startDate 开始日期
     * @return 订单完成时间统计
     */
    @Select("""
        SELECT 
            model.id,
            model.bill_no,
            model.add_date,
            model.finish_time,
            TIMESTAMPDIFF(MINUTE, model.add_date, model.finish_time) as complete_minutes
        FROM bs_order model
        WHERE 1=1
            AND model.finish_time IS NOT NULL
            AND model.shop_id = #{shopId}
            AND model.add_date >= #{startDate}
        ORDER BY model.add_date DESC
    """)
    List<Map<String, Object>> getOrderCompleteTimeStatistics(@Param("shopId") Long shopId, 
                                                            @Param("startDate") LocalDateTime startDate);

    /**
     * 动态条件查询
     * @param conditions 查询条件
     * @return 订单列表
     */
    @Select("""
        <script>
        SELECT 
            model.id,
            model.bill_no,
            model.add_date,
            model.customer_id,
            model.total_amount,
            model.amount,
            model.pay_amount,
            model.pay_state,
            model.order_state,
            model.shop_id,
            model.name
        FROM bs_order model
        WHERE 1=1
        <if test="shopId != null">
            AND model.shop_id = #{shopId}
        </if>
        <if test="customerId != null">
            AND model.customer_id = #{customerId}
        </if>
        <if test="orderState != null">
            AND model.order_state = #{orderState}
        </if>
        <if test="payState != null">
            AND model.pay_state = #{payState}
        </if>
        <if test="startDate != null">
            AND model.add_date >= #{startDate}
        </if>
        <if test="endDate != null">
            AND model.add_date <= #{endDate}
        </if>
        <if test="minAmount != null">
            AND model.total_amount >= #{minAmount}
        </if>
        <if test="maxAmount != null">
            AND model.total_amount <= #{maxAmount}
        </if>
        ORDER BY model.add_date DESC
        </script>
    """)
    List<Order> findOrdersByConditions(Map<String, Object> conditions);
} 